/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import DTO.*;
import UTIL.ConvertString;
import UTIL.SqlDataAccessHelper;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author trquanglong
 */
public class Task2DAO {
    /**
     * Add Task to Database
     * @param task
     * @throws java.sql.SQLException
     * @throws java.io.UnsupportedEncodingException
     */
    public static void addTaskToDB(Task2DTO task) throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Insert into Tasks2 (Subject, StartedDay, EndDay, Status, Priority, Complete, Owner)" +
                    " Values ('" + ConvertString.ConvertString2UTF8(task.getSubject()) + "', '" + ConvertString.ConvertString2UTF8(task.getStartedDay()) + "', '" +
                    ConvertString.ConvertString2UTF8(task.getEndDay()) + "', '" + ConvertString.ConvertString2UTF8(task.getStatus()) + "', '" +
                    ConvertString.ConvertString2UTF8(task.getPriority()) + "', " + Integer.toString(task.getComplete()) + ", '" +
                    ConvertString.ConvertString2UTF8(task.getOwner()) + "')";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Delete one task in table Task2
     * @param task
     * @throws java.sql.SQLException
     * @throws java.io.UnsupportedEncodingException
     */
    public static void deleteOneTask(Task2DTO task) throws SQLException, UnsupportedEncodingException {
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
           // String tmp = ConvertString.ConvertString2UTF8(task.getSubject());
            String sqlQuery = "Delete * from Tasks2 Where Subject = '" + ConvertString.ConvertString2UTF8(task.getSubject()) + "' and StartedDay = '" + ConvertString.ConvertString2UTF8(task.getStartedDay())
                    + "' " +
                    " and EndDay =  '" + ConvertString.ConvertString2UTF8(task.getEndDay()) + "' and Status = '" + ConvertString.ConvertString2UTF8(task.getStatus())
                    + "' and Priority = '"  + ConvertString.ConvertString2UTF8(task.getPriority()) + "' " +
                    " and Complete = " + Integer.toString(task.getComplete()) + " and Owner = '" + ConvertString.ConvertString2UTF8(task.getOwner()) + "'";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex){
        }
        connect.close();
    }

    /**
     * Get all of tasks of table Task2
     * @return
     * @throws java.io.UnsupportedEncodingException
     * @throws java.sql.SQLException
     */
    public static ArrayList<Task2DTO> getListTask() throws UnsupportedEncodingException, SQLException{

        Connection connect = null;
        ArrayList<Task2DTO> alTask = new ArrayList<Task2DTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "select * from Tasks2";
            ResultSet rs = stmt.executeQuery(sqlQuery);

            while(rs.next()){
                Task2DTO task = new Task2DTO();
                task.setIdTask(rs.getInt("ID"));
                task.setSubject(ConvertString.ConvertUTF82String(rs.getString("Subject")));
                task.setStartedDay(ConvertString.ConvertUTF82String(rs.getString("StartedDay")));
                task.setEndDay(ConvertString.ConvertUTF82String(rs.getString("EndDay")));
                task.setStatus(ConvertString.ConvertUTF82String(rs.getString("Status")));
                task.setPriority(ConvertString.ConvertUTF82String(rs.getString("Priority")));
                task.setOwner(ConvertString.ConvertUTF82String(rs.getString("Owner")));
                task.setComplete(rs.getInt("Complete"));
                alTask.add(task);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return alTask;
    }


    /**
     * Delete all of tasks
     * @throws java.sql.SQLException
     */
    public static void deleteAllTask() throws SQLException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Tasks2";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Update information of all of tasks
     *
     */

    public static void updateAllTask(ArrayList<Task2DTO> lstTask) throws SQLException, UnsupportedEncodingException{
        deleteAllTask();
        for (int i = 0; i < lstTask.size(); i++)
            addTaskToDB(lstTask.get(i));
    }

}
